package com.ztesoft.web.common.customdb;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.MessageFormat;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.StringUtils;

import com.ztesoft.framework.util.Utils;
import com.ztesoft.web.common.Configuration;
import com.ztesoft.web.common.DMSConstant;

public final class SQLHelper {
	
	private SQLHelper () {};
	

	/**
	 * 目前配置oracle/informix/mysql分页STR，其余待添加补充
	 * @param keyColumns
	 * @param table
	 * @param scanConditions
	 * @param dbType
	 * @return
	 */
	public static String selectPageByCondition(String keyColumns, String table, String scanConditions, String dbType) {
		String pageSql = null;
		if (DMSConstant.ORACLE.equals(dbType)) {
			pageSql = Configuration.getString("ORACLE_PAGESQL");
		} else if (DMSConstant.MYSQL.equals(dbType)) {
			pageSql = Configuration.getString("MYSQL_PAGESQL");
		} else if (DMSConstant.INFORMIX.equals(dbType)) {
			pageSql = Configuration.getString("INFORMIX_PAGESQL");
		} else if (DMSConstant.MDB.equals(dbType)) {
		}
		if (Utils.notEmpty(pageSql)) {		
			pageSql = MessageFormat.format(pageSql, keyColumns, table, inTableWhere(scanConditions));
		}
		return pageSql;
	}
	
	public static String selectByCondition(String keyColumns, String table, String scanCondition, String limit, String dbType) {
		StringBuilder sqlBuilder = new StringBuilder(" SELECT ");
		if (DMSConstant.ORACLE.equals(dbType)) {
			sqlBuilder.append(keyColumns).append(" FROM ").append(table).append(inTableWhere(scanCondition))
				.append(" AND ROWNUM <= ").append(limit);
		} else if (DMSConstant.MYSQL.equals(dbType)) {
			sqlBuilder.append(keyColumns).append(" FROM ").append(table).append(inTableWhere(scanCondition))
				.append(" LIMIT 0,").append(limit);
		} else if (DMSConstant.INFORMIX.equals(dbType)) {
			sqlBuilder.append( " FIRST ").append(limit).append(" ").append(keyColumns)
				.append(" FROM ").append(table).append(inTableWhere(scanCondition));
		} else if (DMSConstant.MDB.equals(dbType)) {
			sqlBuilder.append(keyColumns).append(" FROM ").append(table).append(inTableWhere(scanCondition));
		}
		return getSqlStr(sqlBuilder.toString());
	}
	
	public static String selectCount(String table, String scanCondition, String limit, String dbType) {
		StringBuilder sqlBuilder = new StringBuilder(" SELECT COUNT(*) FROM ");
		sqlBuilder.append(generateWhereForCount(table, scanCondition, limit, dbType));
		return getSqlStr(sqlBuilder.toString());
	}

	private static String generateWhereForCount(String table, String scanCondition,
			String limit, String dbType) {
		StringBuilder sqlBuilder = new StringBuilder();
		if (DMSConstant.ORACLE.equals(dbType)) {
			sqlBuilder.append(table).append(inTableWhere(scanCondition));
			if (Utils.notEmpty(limit)) {
				sqlBuilder.append(" AND ROWNUM <= ").append(limit);
			}
		} else if (DMSConstant.MYSQL.equals(dbType)) {
			sqlBuilder.append("( SELECT * FROM ");
			sqlBuilder.append(table).append(inTableWhere(scanCondition));
			if (Utils.notEmpty(limit)) {
				sqlBuilder.append(" LIMIT 0,").append(limit);
			}
			sqlBuilder.append(") a");
		} else if (DMSConstant.INFORMIX.equals(dbType) || DMSConstant.MDB.equals(dbType)) {
			sqlBuilder.append(table).append(inTableWhere(scanCondition));
		}
		return sqlBuilder.toString();
	}
	
	private static String inTableWhere(String scanCondition) {
		if (StringUtils.hasText(scanCondition)) {			
			return " WHERE " + scanCondition;
		} else {
			return " ";
		}

	}
	public static void main(String[] args) {
		System.err.println(selectCount("table_", "1=2", "10", "oracle"));
		System.err.println(selectCount("table_", "1=2", "10", "mysql"));
		System.err.println(selectCount("table_", "1=2", "10", "informix"));
		System.err.println(selectCount("table_", "1=2", "10", "mdb"));
	}
	
	/**
	 * @see org.springframework.jdbc.support.JdbcUtils
	 * 
	 * Check whether the given SQL type is numeric.
	 * @param sqlType the SQL type to be checked
	 * @return whether the type is numeric
	 */
	public static boolean isNumeric(int sqlType) {
		return Types.BIT == sqlType || Types.BIGINT == sqlType || Types.DECIMAL == sqlType ||
				Types.DOUBLE == sqlType || Types.FLOAT == sqlType || Types.INTEGER == sqlType ||
				Types.NUMERIC == sqlType || Types.REAL == sqlType || Types.SMALLINT == sqlType ||
				Types.TINYINT == sqlType;
	}
	
	public static boolean isString(int sqlType) {
		return Types.VARCHAR == sqlType || Types.NCHAR == sqlType
				|| Types.CHAR == sqlType;
	}
	
	public static boolean isDate(int sqlType) {
		return Types.DATE  == sqlType || Types.TIME == sqlType 
				|| Types.TIMESTAMP == sqlType;
	}

	/**
	 * oracle需要用函数，另有其他数据库需要用到特殊函数，补上
	 * @param sqlBuilder
	 * @param columnName
	 * @param columeValue
	 * @param sqlType
	 * @param dbType
	 * @return
	 */
	public static String setValue(StringBuilder sqlBuilder, String columnName, String columeValue, int sqlType, String dbType) {
		sqlBuilder.append(columnName).append(" = ");
		if (isNumeric(sqlType)) {//数字
			sqlBuilder.append(columeValue);
		} /*else if (isString(sqlType)){//字符串
			sqlBuilder.append("'").append(columeValue).append("'");
		} */else if (isDate(sqlType) && DMSConstant.ORACLE.equals(dbType)){//时间
				sqlBuilder.append("to_date('").append(columeValue).append("','yyyy-MM-dd hh24:mi:ss')");
		} else {
			sqlBuilder.append("'").append(columeValue).append("'");
		}
		return sqlBuilder.toString();
	}
	
	/**
	 * 设置参数
	 * @param ps
	 * @param vList
	 * @param dbType
	 * @param dataTypeMap 
	 * @throws SQLException
	 */
	public static void setValue(PreparedStatement ps, List<String> vList, String dbType, Map<String, Integer> dataTypeMap,String keyName) throws SQLException {
		int dloop = 1;
		if (DMSConstant.ORACLE.equals(dbType)) {
			int dataTypeCode = 0;
			String[] keyNames = keyName.split(",");
			for(int i=0;i<vList.size();i++){
				dataTypeCode = dataTypeMap.get(keyNames[i].toLowerCase());
				if(isDate(dataTypeCode)){
//					ps.setString(dloop, "to_date('"+vList.get(i)+"','yyyy-MM-dd hh24:mi:ss')");
					ps.setTimestamp(dloop++, Timestamp.valueOf(vList.get(i)));
				}else{
					ps.setString(dloop++, vList.get(i));
				}
			}
		}else{
			for(int i=0;i<vList.size();i++){
				ps.setString(dloop++, vList.get(i));
			}
		}
	}
	
	public static void setValue(PreparedStatement ps, String[] vArr, String dbType, Map<String, Integer> dataTypeMap,String[] keyNames) throws SQLException {
		int dloop = 1;
		if (DMSConstant.ORACLE.equals(dbType)) {
			int dataTypeCode = 0;
			for(int i=0;i<vArr.length;i++){
				dataTypeCode = dataTypeMap.get(keyNames[i].toLowerCase());
				if(isDate(dataTypeCode)){
//					ps.setString(dloop++, "to_date('"+vArr[i]+"','yyyy-MM-dd hh24:mi:ss')");
					ps.setTimestamp(dloop++, Timestamp.valueOf(vArr[i]));
				}else{
					ps.setString(dloop++, vArr[i]);
				}
			}
		}else{
			for(int i=0;i<vArr.length;i++){
				ps.setString(dloop++, vArr[i]);
			}
		}
	}
	
	/**
	 * 拼接where
	 * @param sqlBuilder
	 * @param columnArr
	 */
	public static void generateWhere(StringBuilder sqlBuilder,
			String[] columnArr) {
		sqlBuilder.append(" where ");
		for (int i = 0, j = columnArr.length; i < j; i++) {
			if (i == j-1) {					
				sqlBuilder.append(columnArr[i]).append(" = ? ");
			} else {							
				sqlBuilder.append(columnArr[i]).append(" = ? ").append("and ");
			}
		}
	}


	private static String getSqlStr(String sql) {
		if (sql.contains("1=1 and ")) {
			sql = sql.replace("1=1 and ", " ");
		} else if (sql.contains("where 1=1)")) { 
			sql = sql.replace("where 1=1", " ");
		}
		return sql;
	}
	
	public static int countRows(JdbcTemplate jdbcTemplate, String sql) {
		return jdbcTemplate.queryForObject(sql, Integer.class);
	}
}
